package com.cmnit.analysis.dao

import com.cmnit.analysis.common.{TDao, TSql}
import org.apache.log4j.Logger
import org.apache.spark.sql.DataFrame

class GantryLicencePlateDao extends TSql with TDao {
  private val logger: Logger = Logger.getLogger(this.getClass)

  /**
   * 获取来源数据（门架牌识）
   */
  def gantryVehdisData(sqlTime: String): DataFrame = {
    sparkSQL(
      "select " +
        "* " +
        "from " +
        "ods.ods_etc_gantryvehdisdata " +
        "where "
        + sqlTime +
        "and vehiclePlate not like '%挂'")
  }

  /**
   * 门架牌识宽表
   */
  def gantryVehdisDataDetail(): DataFrame = {
    sparkSQL(
      "select " +
        "t.picId, " +
        "t.gantryId, " +
        "tmp.gantryname, " +
        "tmp.gantrytype, " +
        "tmp.province, " +
        "t.pictime, " +
        "t.driveDir, " +
        "t.cameraNum, " +
        "t.hourBatchNo, " +
        "t.laneNum, " +
        "t.vehiclePlate, " +
        "getVlp(t.vehicleplate) as vlp, " +
        "getVlpc(t.vehicleplate) as vlpc " +
        "from temp_gantryvehdisdata t " +
        "join " +
        "tmp_organization_gantryinfo tmp " +
        "on tmp.gantryid = t.gantryid ")
  }

  /**
   * 省界门架流量（结果表）
   */
  def provinceGantryVehdisData(acctDate: String): DataFrame = {
    sparkSQL(
      "select " +
        "t.province," +
        "t.gantryid," +
        "t.gantrytype," +
        "t.vehicletype," +
        "count(t.vehicleplate) as countnum," +
        "'" + acctDate + "' as statisday " +
        "from " +
        "(select " +
        "a.*," +
        "nvl(b.vehicletype, 1) vehicletype " +
        "from " +
        "(select " +
        "distinct " +
        "m.vehicleplate, " +
        "m.province, " +
        "substr(m.gantryid,1,17) as gantryid, " +
        "m.gantryname, " +
        "m.gantrytype " +
        "from " +
        "temp_gantryvehdisdatadetail m) a " +
        "left join " +
        "tmp_tbl_vehiclebasicinfo b " +
        "on a.vehicleplate = b.vehicleplate) t " +
        "group by t.province,t.gantryid,t.gantrytype,t.vehicletype")
  }
}
